package fr.mywiki.model.queries;

import java.io.ByteArrayInputStream;
import java.rmi.RemoteException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import fr.mywiki.business.entry.EntryLite;
import fr.mywiki.business.file.FileLite;
import fr.mywiki.business.versioning.MultiversionLite;
import fr.mywiki.business.versioning.VersionLite;
import fr.mywiki.model.ejbAdapter.DataController;
import fr.mywiki.model.ejbAdapter.utils.PersistenceException;
import fr.mywiki.model.ejbAdapter.utils.Sequencer;

/**
 * Retrieves, stores and manipulates the multiversion objects stored in the
 * database. The content of a multiversion object is zipped if and only if it is
 * linked to several versions.
 * 
 * @author tlombard
 */
public class DatabaseMultiversionController {
	/**
	 * Creates a new multiversion entry in the database and returns a
	 * MultiversionLite linked to it.
	 * 
	 * @return an empty <code>MultiversionLite</code> object.
	 * @throws RemoteException
	 *           if a SQLException was caught.
	 */
	public static MultiversionLite createMultiversion()
			throws PersistenceException, RemoteException {
		Long key = new Long(Sequencer.getInstance(FileLite.JNDI_NAME).next());
		try {
			while (DatabaseMultiversionController.findMultiversionByPrimaryKey(key) != null)
				key = new Long(Sequencer.getInstance(MultiversionLite.JNDI_NAME).next());
			return createMultiversion(key);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}

	/**
	 * Creates a new multiversion object in the database, this object having the
	 * specified identifier.
	 * 
	 * @param id
	 *          the identifier of the object to create.
	 * @return a <code>MultiversionLite</code> object representing the created
	 *         multiversion.
	 * @throws SQLException
	 *           if the insertion could not be done.
	 * @version 20080309 : initiates the property column to NULL.
	 */
	protected static MultiversionLite createMultiversion(Long id)
			throws SQLException {
		String sql = "INSERT INTO multiversion VALUES (" + id + ", NULL, NULL, NULL)";
		DatabaseAdapter.executeUpdateQuery(sql);

		MultiversionLite multiversion = new MultiversionLite();
		multiversion.setId(id);
		return multiversion;
	}

	/**
	 * Returns the representative object (containing of the version given in
	 * parameter.
	 * 
	 * @param version
	 *          a <code>VersionLite</code> object.
	 * @return a <code>MultiversionLite</code> object.
	 */
	public static MultiversionLite findMultiversionByPrimaryKey(Long id)
			throws RemoteException {
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, property, content, count FROM multiversion WHERE id=" + id;

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			if (resultSet.next()) {
				Blob blob = resultSet.getBlob(2);
				return new MultiversionLite(
						new Long(resultSet.getLong(1)),
						resultSet.getString(2), 
						new Integer(resultSet.getInt(3)),
						blob.getBytes(1L,	(int) blob.length()));
			} else
				return null;
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}

	/**
	 * Retrieves all the <code>MultiversionLite</code> objects with only one
	 * version inside and linked to the entry given in parameter for the given
	 * property.
	 * 
	 * @param lite
	 *          a <code>EntryLite</code> object
	 * @param property
	 *          the name of the considered property.
	 * @return a <code>List</code> of <code>MultiversionLite</code> objects,
	 *         sorted by the date of version.
	 */
	public static List getIsolatedMultiversions(EntryLite lite, String property)
			throws RemoteException {
		List list = new ArrayList();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			// Select isolated multiversion objects linked to the entry and sort by
			// date, from first to last
			String sql = "SELECT m.id, m.content, m.count, v."
					+ VersionLite.FIELD_DATE
					+ " FROM multiversion m, version v, entry a WHERE m."
					+ MultiversionLite.FIELD_PROPERTY + "='" + property + "' AND m."
					+ MultiversionLite.FIELD_COUNT + "=1 AND m.id=v."
					+ VersionLite.FIELD_MULTIVERSION + " AND v."
					+ VersionLite.LINK_VERSION_ENTRY + "=a." + EntryLite.FIELD_ID
					+ " ORDER BY v." + VersionLite.FIELD_DATE;

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next()) {
				Blob blob = resultSet.getBlob(2);
				list.add(new MultiversionLite(
						new Long(resultSet.getLong(1)),
						property,
						new Integer(resultSet.getInt(3)),
						blob.getBytes(1L, (int) blob.length())));
			}
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
		return list;
	}

	/**
	 * Returns either the <code>List</code> of versions linked to
	 * <code>lite</code>. The list is eventually reduced to one multiversion.
	 * 
	 * @param lite
	 *          a <code>MultiversionLite</code> object
	 * @return a <code>List</code> of <code>VersionLite/code> objects
	 */
	public static List getVersions(MultiversionLite lite) throws RemoteException {
		List list = new ArrayList();
		
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id FROM version WHERE "
					+ VersionLite.FIELD_MULTIVERSION + "=" + lite.getId();
			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next()) {
				String id = resultSet.getString(1);
				list.add(DataController.getValue(new Long(id),
						VersionLite.TYPE_ENT));
			}
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
		
		return list;
	}
	
	/**
	 * Updates a multiversion object in the database. The multiversion object must already exist.
	 * 
	 * @param multiversion a <code>MultiversionLite</code> object.
	 * @throws RemoteException if a SQLException has been caught.
	 */
	public static void updateMultiversion(MultiversionLite multi) throws RemoteException {
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "UPDATE multiversion SET property=?, content=?, count=? WHERE id="
				+ multi.getId().toString();
			
			PreparedStatement stat = con.prepareStatement(sql);
			
			stat.setString(1,multi.getProperty());
			stat.setBinaryStream(2, 
					new ByteArrayInputStream(multi.getContent()), multi.getContent().length);
			stat.setInt(3,multi.getCount().intValue());
			
			stat.execute();
			DatabaseAdapter.closeConnection(con);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Removes a record from the database.
	 * 
	 * @param id <code>Long</code> identifier of the record to remove.
	 * @throws RemoteException if a SQLException was caught.
	 */
	public static void removeMultiversion(Long id) throws RemoteException {
		String sql = "DELETE FROM multiversion WHERE id="+id.toString();
		try {
			DatabaseAdapter.executeUpdateQuery(sql);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Returns all the multiversion objects of the database.
	 * 
	 * @return a <code>Collection</code> of <code>Multiversion</code> objects
	 */
	public static Collection findAllMultiversion() throws RemoteException {
		Collection objects = new ArrayList();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, property, content, count FROM multiversion";

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next()) {
				Blob blob = resultSet.getBlob(3);
				objects.add(
					new MultiversionLite(
							new Long(resultSet.getLong(1)),
							resultSet.getString(2),
							new Integer(resultSet.getInt(4)),
							blob == null ? null : blob.getBytes(1L, (int) blob.length())));
			}
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
		
		return objects;
	}
}
